{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 外部数据获取及分享\n",
    "(https://www.joinquant.com/view/community/detail/b25a17821b24d57faa6ec0291c51af09?type=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 行业类"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.申万二级及三级的行情及估值数据ok\n",
    "作者: ssk 原帖链接:https://www.joinquant.com/view/community/detail/12315"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "code_folding": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SwIndexCode</th>\n",
       "      <th>SwIndexName</th>\n",
       "      <th>OpenIndex</th>\n",
       "      <th>CloseIndex</th>\n",
       "      <th>MaxIndex</th>\n",
       "      <th>MinIndex</th>\n",
       "      <th>BargainAmount</th>\n",
       "      <th>BargainSum</th>\n",
       "      <th>Markup</th>\n",
       "      <th>TurnoverRate</th>\n",
       "      <th>PE</th>\n",
       "      <th>PB</th>\n",
       "      <th>MeanPrice</th>\n",
       "      <th>BargainSumRate</th>\n",
       "      <th>NegotiablesShareSum</th>\n",
       "      <th>NegotiablesShareSum2</th>\n",
       "      <th>DP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BargainDate</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-02-25</th>\n",
       "      <td>850111</td>\n",
       "      <td>种子生产</td>\n",
       "      <td>2493.65</td>\n",
       "      <td>2603.85</td>\n",
       "      <td>2612.97</td>\n",
       "      <td>2469.67</td>\n",
       "      <td>18544</td>\n",
       "      <td>109029</td>\n",
       "      <td>4.57</td>\n",
       "      <td>3.6043</td>\n",
       "      <td>45.49</td>\n",
       "      <td>2.68</td>\n",
       "      <td>6.70</td>\n",
       "      <td>0.10</td>\n",
       "      <td>3496540.23</td>\n",
       "      <td>437067.53</td>\n",
       "      <td>0.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-26</th>\n",
       "      <td>850111</td>\n",
       "      <td>种子生产</td>\n",
       "      <td>2601.41</td>\n",
       "      <td>2577.02</td>\n",
       "      <td>2643.98</td>\n",
       "      <td>2534.83</td>\n",
       "      <td>20089</td>\n",
       "      <td>115323</td>\n",
       "      <td>-1.03</td>\n",
       "      <td>3.9047</td>\n",
       "      <td>45.02</td>\n",
       "      <td>2.65</td>\n",
       "      <td>6.65</td>\n",
       "      <td>0.11</td>\n",
       "      <td>3470405.45</td>\n",
       "      <td>433800.68</td>\n",
       "      <td>0.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-27</th>\n",
       "      <td>850111</td>\n",
       "      <td>种子生产</td>\n",
       "      <td>2571.52</td>\n",
       "      <td>2547.74</td>\n",
       "      <td>2603.46</td>\n",
       "      <td>2530.19</td>\n",
       "      <td>13651</td>\n",
       "      <td>78331</td>\n",
       "      <td>-1.14</td>\n",
       "      <td>2.6533</td>\n",
       "      <td>44.51</td>\n",
       "      <td>2.62</td>\n",
       "      <td>6.58</td>\n",
       "      <td>0.09</td>\n",
       "      <td>3430769.77</td>\n",
       "      <td>428846.22</td>\n",
       "      <td>0.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-28</th>\n",
       "      <td>850111</td>\n",
       "      <td>种子生产</td>\n",
       "      <td>2550.00</td>\n",
       "      <td>2559.18</td>\n",
       "      <td>2584.13</td>\n",
       "      <td>2523.73</td>\n",
       "      <td>8255</td>\n",
       "      <td>50326</td>\n",
       "      <td>0.45</td>\n",
       "      <td>1.6044</td>\n",
       "      <td>44.71</td>\n",
       "      <td>2.64</td>\n",
       "      <td>6.62</td>\n",
       "      <td>0.08</td>\n",
       "      <td>3449990.37</td>\n",
       "      <td>431248.80</td>\n",
       "      <td>0.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-03-01</th>\n",
       "      <td>850111</td>\n",
       "      <td>种子生产</td>\n",
       "      <td>2567.25</td>\n",
       "      <td>2570.26</td>\n",
       "      <td>2590.56</td>\n",
       "      <td>2519.63</td>\n",
       "      <td>9037</td>\n",
       "      <td>53291</td>\n",
       "      <td>0.43</td>\n",
       "      <td>1.7564</td>\n",
       "      <td>44.91</td>\n",
       "      <td>2.65</td>\n",
       "      <td>6.64</td>\n",
       "      <td>0.08</td>\n",
       "      <td>3462555.34</td>\n",
       "      <td>432819.42</td>\n",
       "      <td>0.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            SwIndexCode SwIndexName OpenIndex CloseIndex MaxIndex MinIndex  \\\n",
       "BargainDate                                                                  \n",
       "2019-02-25       850111        种子生产   2493.65    2603.85  2612.97  2469.67   \n",
       "2019-02-26       850111        种子生产   2601.41    2577.02  2643.98  2534.83   \n",
       "2019-02-27       850111        种子生产   2571.52    2547.74  2603.46  2530.19   \n",
       "2019-02-28       850111        种子生产   2550.00    2559.18  2584.13  2523.73   \n",
       "2019-03-01       850111        种子生产   2567.25    2570.26  2590.56  2519.63   \n",
       "\n",
       "            BargainAmount BargainSum Markup TurnoverRate     PE    PB  \\\n",
       "BargainDate                                                             \n",
       "2019-02-25          18544     109029   4.57       3.6043  45.49  2.68   \n",
       "2019-02-26          20089     115323  -1.03       3.9047  45.02  2.65   \n",
       "2019-02-27          13651      78331  -1.14       2.6533  44.51  2.62   \n",
       "2019-02-28           8255      50326   0.45       1.6044  44.71  2.64   \n",
       "2019-03-01           9037      53291   0.43       1.7564  44.91  2.65   \n",
       "\n",
       "            MeanPrice BargainSumRate NegotiablesShareSum NegotiablesShareSum2  \\\n",
       "BargainDate                                                                     \n",
       "2019-02-25       6.70           0.10          3496540.23            437067.53   \n",
       "2019-02-26       6.65           0.11          3470405.45            433800.68   \n",
       "2019-02-27       6.58           0.09          3430769.77            428846.22   \n",
       "2019-02-28       6.62           0.08          3449990.37            431248.80   \n",
       "2019-03-01       6.64           0.08          3462555.34            432819.42   \n",
       "\n",
       "               DP  \n",
       "BargainDate        \n",
       "2019-02-25   0.57  \n",
       "2019-02-26   0.58  \n",
       "2019-02-27   0.59  \n",
       "2019-02-28   0.58  \n",
       "2019-03-01   0.58  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#获取申万官网申万行业数据\n",
    "\n",
    "#导入库\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import requests\n",
    "import json\n",
    "from datetime import timedelta,date\n",
    "\n",
    "\n",
    "# 获取申万官网申万行业数据\n",
    "# code：行业代码  https://www.joinquant.com/help/api/help?name=plateData#申万行业\n",
    "# frequency：day/week/month\n",
    "# start_date：None(表示最早日期)\n",
    "# end_date：None(表示今天日期)\n",
    "# fields：None(表示所有字段)\n",
    "def get_sw_data(code=None,start_date=None,end_date=None,frequency='day',fields=None): \n",
    "    #headers\n",
    "    header={\n",
    "        'HOST':'www.swsindex.com',\n",
    "        'Referer':'http://www.swsindex.com/idx0200.aspx?columnid=8838&type=Day',\n",
    "        'User-Agent':'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) \\\n",
    "    Chrome/53.0.2785.104 Safari/537.36 Core/1.53.4482.400 QQBrowser/9.7.13001.400'\n",
    "        }\n",
    "\n",
    "    #传入参数\n",
    "    param={\n",
    "        'tablename':'V_Report',\n",
    "        'key':'id',\n",
    "        #页面序号，每页返回20条数据\n",
    "        'p':'1',\n",
    "        #查询语句，查询的代码、日期、数据类型\n",
    "        \"where\":\"swindexcode in ('801020') and   BargainDate>='2018-04-02' and  BargainDate<='2018-04-24' and type='Day'\",\n",
    "        #排序(swindexcode asc表示按照代码升序，BargainDate_1表示按照日期降序，_2表示按照升序)\n",
    "        'orderby':'swindexcode asc,BargainDate_2',\n",
    "        #返回的字段\n",
    "        'fieldlist':'SwIndexCode,SwIndexName,BargainDate,OpenIndex,CloseIndex,MaxIndex,MinIndex,BargainAmount,BargainSum,Markup,TurnoverRate,\\\n",
    "    PE,PB,MeanPrice,BargainSumRate,NegotiablesShareSum,NegotiablesShareSum2,DP',\n",
    "        'pagecount':'1',\n",
    "        'timed':'1524497094532',\n",
    "        }\n",
    "    \n",
    "    #数据表表头\n",
    "    sw_columns_list=['SwIndexCode','SwIndexName','BargainDate','OpenIndex','CloseIndex','MaxIndex','MinIndex','BargainAmount','BargainSum',\n",
    "         'Markup','TurnoverRate','PE','PB','MeanPrice','BargainSumRate','NegotiablesShareSum','NegotiablesShareSum2','DP']\n",
    "\n",
    "    #数据类型（日、周、月）\n",
    "    frequency_list=['day','week','month']\n",
    "\n",
    "\n",
    "    #配置查询语句\n",
    "    where=\"swindexcode in (\"\n",
    "    if code is None:\n",
    "        #如果代码为空，则代码为代码列表\n",
    "        code='801010'\n",
    "    else:    \n",
    "        if type(code)==list:\n",
    "            code_str=str(code).replace('[','').replace(']','')\n",
    "        if type(code)==str:\n",
    "            code_str=\"'\"+code+\"'\"\n",
    "\n",
    "    where+=code_str   \n",
    "    \n",
    "    #配置日期\n",
    "#     today_str=pd.datetime.today().strftime('%Y-%m-%d')\n",
    "    today_str=date.today().strftime('%Y-%m-%d')\n",
    "    if (start_date is None) or (start_date<'1999-12-30') or (start_date>today_str):\n",
    "        start_date='1999-12-30'\n",
    "    where+=\") and BargainDate>='\"     \n",
    "    where+=start_date\n",
    "    if (end_date is None) or (end_date>today_str) or (end_date<'1999-12-30'):\n",
    "        end_date=today_str\n",
    "    where+=\"' and BargainDate<='\" \n",
    "    where+=end_date  \n",
    "    \n",
    "    #配置数据类型\n",
    "    if not(frequency in frequency_list):  \n",
    "        frequency='day'\n",
    "    where+=\"' and type='\"\n",
    "    where+=frequency\n",
    "    where+=\"'\"\n",
    "    param['where']=where \n",
    "    \n",
    "    #配置字段\n",
    "    columns=sw_columns_list\n",
    "    fieldlist=str(sw_columns_list).replace(\" \",\"\").replace(\"'\",\"\").replace('[',\"\").replace(']',\"\")   \n",
    "    if not(fields is None):\n",
    "        if(set(fields).issubset(set(sw_columns_list))):  \n",
    "            if not (['SwIndexCode','SwIndexName','BargainDate'] in fields):\n",
    "                fields=['SwIndexCode','SwIndexName','BargainDate']+fields\n",
    "        fieldlist=str(fields).replace(\" \",\"\").replace(\"'\",\"\").replace('[',\"\").replace(']',\"\") \n",
    "        columns=fields\n",
    "    param['fieldlist']=fieldlist\n",
    "    \n",
    "    df=pd.DataFrame()\n",
    "    #url\n",
    "    url='http://www.swsindex.com/handler.aspx'\n",
    "    #页面计数器\n",
    "    page=1\n",
    "    while True:\n",
    "        #获取数据\n",
    "        ret=requests.get(url,data=param,headers=header)\n",
    "        if not (ret.ok is True):\n",
    "            break\n",
    "        #整理引号、日期格式    \n",
    "        data=ret.text.replace(\"'\", '\"').replace(' 0:00:00','').replace('/','-')\n",
    "        #解析数据\n",
    "        data=json.loads(data).get('root')\n",
    "        if len(data)==0:\n",
    "            break\n",
    "        #追加数据表    \n",
    "        df=df.append(pd.DataFrame(data,columns=columns))\n",
    "        #设置页面计数器\n",
    "        page+=1\n",
    "        param['p']=str(page)    \n",
    "    if len(df)!=0:   \n",
    "        df.BargainDate=pd.to_datetime(df.BargainDate,format='%Y-%m-%d')\n",
    "        \n",
    "    #返回数据\n",
    "    return df.set_index('BargainDate')\n",
    "df=get_sw_data('850111',start_date='2019-02-23')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "****\n",
    "# 股票类数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.东财股吧热门帖ok"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "start get html 0 -> 50\n",
      "start convert data\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>550</td>\n",
       "      <td>17</td>\n",
       "      <td></td>\n",
       "      <td>清华大学新媒体研究中心沈阳表示：元宇宙核心技术主要</td>\n",
       "      <td>http://guba.eastmoney.com/news,cjpl,1109450952...</td>\n",
       "      <td>股市信息速传</td>\n",
       "      <td>//i.eastmoney.com/8774113785838342</td>\n",
       "      <td>11-28 19:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5888</td>\n",
       "      <td>30</td>\n",
       "      <td></td>\n",
       "      <td>大商所：自2021年11月25日夜盘起对棕榈油、铁矿期货交</td>\n",
       "      <td>http://guba.eastmoney.com/news,cjpl,1103626487...</td>\n",
       "      <td>财经评论</td>\n",
       "      <td>//i.eastmoney.com/9313013693864916</td>\n",
       "      <td>11-21 15:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1585</td>\n",
       "      <td>21</td>\n",
       "      <td></td>\n",
       "      <td>康恩贝双十一销售总额超1.8亿元</td>\n",
       "      <td>http://guba.eastmoney.com/news,cjpl,1103573738...</td>\n",
       "      <td>康恩贝资讯</td>\n",
       "      <td>/list,cjpl.html</td>\n",
       "      <td>11-19 16:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1828</td>\n",
       "      <td>25</td>\n",
       "      <td></td>\n",
       "      <td>四川美丰：拟对分（子）公司生产装置进行检修</td>\n",
       "      <td>http://guba.eastmoney.com/news,cjpl,1103616696...</td>\n",
       "      <td>四川美丰资讯</td>\n",
       "      <td>/list,cjpl.html</td>\n",
       "      <td>11-19 15:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8679</td>\n",
       "      <td>171</td>\n",
       "      <td></td>\n",
       "      <td>*ST众泰：没有重大资产重组</td>\n",
       "      <td>http://guba.eastmoney.com/news,cjpl,1103619090...</td>\n",
       "      <td>*ST众泰资讯</td>\n",
       "      <td>/list,cjpl.html</td>\n",
       "      <td>11-19 15:30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      0    1 2                              3  \\\n",
       "0   550   17        清华大学新媒体研究中心沈阳表示：元宇宙核心技术主要   \n",
       "1  5888   30    大商所：自2021年11月25日夜盘起对棕榈油、铁矿期货交   \n",
       "2  1585   21                 康恩贝双十一销售总额超1.8亿元   \n",
       "3  1828   25            四川美丰：拟对分（子）公司生产装置进行检修   \n",
       "4  8679  171                   *ST众泰：没有重大资产重组   \n",
       "\n",
       "                                                   4        5  \\\n",
       "0  http://guba.eastmoney.com/news,cjpl,1109450952...   股市信息速传   \n",
       "1  http://guba.eastmoney.com/news,cjpl,1103626487...     财经评论   \n",
       "2  http://guba.eastmoney.com/news,cjpl,1103573738...    康恩贝资讯   \n",
       "3  http://guba.eastmoney.com/news,cjpl,1103616696...   四川美丰资讯   \n",
       "4  http://guba.eastmoney.com/news,cjpl,1103619090...  *ST众泰资讯   \n",
       "\n",
       "                                    6            7  \n",
       "0  //i.eastmoney.com/8774113785838342  11-28 19:27  \n",
       "1  //i.eastmoney.com/9313013693864916  11-21 15:45  \n",
       "2                     /list,cjpl.html  11-19 16:08  \n",
       "3                     /list,cjpl.html  11-19 15:59  \n",
       "4                     /list,cjpl.html  11-19 15:30  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "def get_post_data(html):\n",
    "    soup = BeautifulSoup(html)\n",
    "    post_list = soup.find_all(\"div\", class_=\"articleh\")\n",
    "    post_list_convert = []\n",
    "    for itm in post_list:\n",
    "        single_post = []\n",
    "        ll = itm.find_all(\"span\")\n",
    "        for idx in range(len(ll)):\n",
    "            if idx == 2:\n",
    "                # 帖子地址和标题\n",
    "                if ll[idx].em:\n",
    "                    single_post.append(ll[idx].em.string)\n",
    "                else:\n",
    "                    single_post.append(\"\")\n",
    "                single_post.append(ll[idx].a.string)\n",
    "                single_post.append(\"http://guba.eastmoney.com\" + ll[idx].a['href'])\n",
    "            elif idx == 3:\n",
    "                # 作者信息\n",
    "                single_post.append(ll[idx].a.string)\n",
    "                single_post.append(ll[idx].a['href'])\n",
    "            else:\n",
    "                # 其他信息\n",
    "                single_post.append(ll[idx].string)\n",
    "        post_list_convert.append(single_post)\n",
    "    return post_list_convert\n",
    "def get_html(urls):\n",
    "    g_ret = []\n",
    "    for itm in urls:\n",
    "        g_ret.append(requests.get(itm))\n",
    "    ret = [itm.content.decode(\"utf-8\") for itm in g_ret]\n",
    "    return ret\n",
    "def control(page_count=20, step = 50):\n",
    "    url_list = []\n",
    "    # 构造url列表\n",
    "    for idx in range(1, page_count + 1):\n",
    "        url_list.append(\"http://guba.eastmoney.com/list,cjpl,99_{}.html\".format(idx))\n",
    "    for idx in range(0, page_count, step):\n",
    "        # 每次爬step页\n",
    "        print(\"start get html {} -> {}\".format(idx, idx + step))\n",
    "        all_html_data = get_html(url_list[idx:idx + step])\n",
    "        print(\"start convert data\")\n",
    "        all_post_data = get_post_data(all_html_data[0])\n",
    "        for itm in all_html_data[1:]:\n",
    "            for itm2 in get_post_data(itm):\n",
    "                all_post_data.append(itm2)\n",
    "        return pd.DataFrame(all_post_data)\n",
    "\n",
    "control(page_count=3).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.获取最新公布的机构调研数据ok"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ChangePercent</th>\n",
       "      <th>Close</th>\n",
       "      <th>CompanyCode</th>\n",
       "      <th>CompanyName</th>\n",
       "      <th>OrgCode</th>\n",
       "      <th>OrgName</th>\n",
       "      <th>OrgSum</th>\n",
       "      <th>SName</th>\n",
       "      <th>NoticeDate</th>\n",
       "      <th>StartDate</th>\n",
       "      <th>EndDate</th>\n",
       "      <th>Place</th>\n",
       "      <th>Description</th>\n",
       "      <th>Orgtype</th>\n",
       "      <th>OrgtypeName</th>\n",
       "      <th>Personnel</th>\n",
       "      <th>Licostaff</th>\n",
       "      <th>Maincontent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SCode</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>688106</th>\n",
       "      <td>2.81</td>\n",
       "      <td>27.77</td>\n",
       "      <td>80206091</td>\n",
       "      <td></td>\n",
       "      <td>80053708</td>\n",
       "      <td>汇添富基金</td>\n",
       "      <td></td>\n",
       "      <td>金宏气体</td>\n",
       "      <td>2021-11-29</td>\n",
       "      <td>2021-11-26</td>\n",
       "      <td></td>\n",
       "      <td>苏州金宏气体总部会议室</td>\n",
       "      <td>特定对象调研</td>\n",
       "      <td>004001</td>\n",
       "      <td>基金管理公司</td>\n",
       "      <td>马翔,陈威</td>\n",
       "      <td>金向华,龚小玲,陈子晗</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688106</th>\n",
       "      <td>2.81</td>\n",
       "      <td>27.77</td>\n",
       "      <td>80206091</td>\n",
       "      <td></td>\n",
       "      <td>80066058</td>\n",
       "      <td>中信建投证券</td>\n",
       "      <td></td>\n",
       "      <td>金宏气体</td>\n",
       "      <td>2021-11-29</td>\n",
       "      <td>2021-11-26</td>\n",
       "      <td></td>\n",
       "      <td>苏州金宏气体总部会议室</td>\n",
       "      <td>特定对象调研</td>\n",
       "      <td>005001</td>\n",
       "      <td>证券公司</td>\n",
       "      <td>孙芳芳,韩非,朱韵</td>\n",
       "      <td>金向华,龚小玲,陈子晗</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688106</th>\n",
       "      <td>2.81</td>\n",
       "      <td>27.77</td>\n",
       "      <td>80206091</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>一典资管</td>\n",
       "      <td></td>\n",
       "      <td>金宏气体</td>\n",
       "      <td>2021-11-29</td>\n",
       "      <td>2021-11-26</td>\n",
       "      <td></td>\n",
       "      <td>苏州金宏气体总部会议室</td>\n",
       "      <td>特定对象调研</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>施晶,蒋枫</td>\n",
       "      <td>金向华,龚小玲,陈子晗</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688106</th>\n",
       "      <td>2.81</td>\n",
       "      <td>27.77</td>\n",
       "      <td>80206091</td>\n",
       "      <td></td>\n",
       "      <td>80384060</td>\n",
       "      <td>高毅资产</td>\n",
       "      <td></td>\n",
       "      <td>金宏气体</td>\n",
       "      <td>2021-11-29</td>\n",
       "      <td>2021-11-26</td>\n",
       "      <td></td>\n",
       "      <td>苏州金宏气体总部会议室</td>\n",
       "      <td>特定对象调研</td>\n",
       "      <td>002007</td>\n",
       "      <td>资产管理公司</td>\n",
       "      <td>杨千里</td>\n",
       "      <td>金向华,龚小玲,陈子晗</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>600163</th>\n",
       "      <td>1.81</td>\n",
       "      <td>8.45</td>\n",
       "      <td>10000127</td>\n",
       "      <td>福建省南纸股份有限公司</td>\n",
       "      <td>80045188</td>\n",
       "      <td>申万菱信基金管理有限公司</td>\n",
       "      <td></td>\n",
       "      <td>中闽能源</td>\n",
       "      <td>2021-11-29</td>\n",
       "      <td>2021-11-26</td>\n",
       "      <td></td>\n",
       "      <td>中闽(平潭)风电有限公司</td>\n",
       "      <td>特定对象调研,现场参观</td>\n",
       "      <td>004001</td>\n",
       "      <td>基金管理公司</td>\n",
       "      <td>廖民兵</td>\n",
       "      <td>董事长 张骏,董事会秘书 段静静,中闽(平潭)风电有限公司 许友华,证券事务专员 张仅</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ChangePercent  Close CompanyCode  CompanyName   OrgCode       OrgName  \\\n",
       "SCode                                                                          \n",
       "688106          2.81  27.77    80206091               80053708         汇添富基金   \n",
       "688106          2.81  27.77    80206091               80066058        中信建投证券   \n",
       "688106          2.81  27.77    80206091                                 一典资管   \n",
       "688106          2.81  27.77    80206091               80384060          高毅资产   \n",
       "600163          1.81   8.45    10000127  福建省南纸股份有限公司  80045188  申万菱信基金管理有限公司   \n",
       "\n",
       "       OrgSum SName  NoticeDate   StartDate EndDate         Place  \\\n",
       "SCode                                                               \n",
       "688106         金宏气体  2021-11-29  2021-11-26           苏州金宏气体总部会议室   \n",
       "688106         金宏气体  2021-11-29  2021-11-26           苏州金宏气体总部会议室   \n",
       "688106         金宏气体  2021-11-29  2021-11-26           苏州金宏气体总部会议室   \n",
       "688106         金宏气体  2021-11-29  2021-11-26           苏州金宏气体总部会议室   \n",
       "600163         中闽能源  2021-11-29  2021-11-26          中闽(平潭)风电有限公司   \n",
       "\n",
       "        Description Orgtype OrgtypeName  Personnel  \\\n",
       "SCode                                                \n",
       "688106       特定对象调研  004001      基金管理公司      马翔,陈威   \n",
       "688106       特定对象调研  005001        证券公司  孙芳芳,韩非,朱韵   \n",
       "688106       特定对象调研                          施晶,蒋枫   \n",
       "688106       特定对象调研  002007      资产管理公司        杨千里   \n",
       "600163  特定对象调研,现场参观  004001      基金管理公司        廖民兵   \n",
       "\n",
       "                                          Licostaff Maincontent  \n",
       "SCode                                                            \n",
       "688106                                  金向华,龚小玲,陈子晗              \n",
       "688106                                  金向华,龚小玲,陈子晗              \n",
       "688106                                  金向华,龚小玲,陈子晗              \n",
       "688106                                  金向华,龚小玲,陈子晗              \n",
       "600163  董事长 张骏,董事会秘书 段静静,中闽(平潭)风电有限公司 许友华,证券事务专员 张仅              "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_jgdy_all(page_count=1,page_size=100):\n",
    "    '''获取最近公布的机构调研数据,count为返回页数,一页为page_size条数据(不得超过5000条),默认返回100条,以发布时间排序'''\n",
    "    url = 'http://data.eastmoney.com/DataCenter_V3/jgdy/xx.ashx?'\n",
    "    l = []\n",
    "    for i in range(1,page_count+1):\n",
    "        param = {\n",
    "        \"pagesize\":page_size,\n",
    "        \"page\":i,\n",
    "        'sortRule':-1,\n",
    "        'sortType':0,\n",
    "        'rt':51777724,}\n",
    "        ret=requests.get(url,params=param)\n",
    "        l.append(pd.DataFrame(json.loads(ret.text)['data']))\n",
    "    return pd.concat(l,axis=0).set_index('SCode')\n",
    "df = get_jgdy_all(1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.获取个股机构调研数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "120897\n"
     ]
    },
    {
     "ename": "TypeError",
     "evalue": "'int' object is not subscriptable",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-8-1c221b7c3dc9>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     14\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     15\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mjson\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloads\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mret\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'data'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 16\u001b[1;33m \u001b[0mget_jgdy_one\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'000568'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m<ipython-input-8-1c221b7c3dc9>\u001b[0m in \u001b[0;36mget_jgdy_one\u001b[1;34m(code)\u001b[0m\n\u001b[0;32m     13\u001b[0m     \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mjson\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloads\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mret\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     14\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 15\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mjson\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloads\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mret\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'data'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     16\u001b[0m \u001b[0mget_jgdy_one\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'000568'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: 'int' object is not subscriptable"
     ]
    }
   ],
   "source": [
    "def get_jgdy_one(code):\n",
    "    '''获取个股调研数据'''\n",
    "    url = 'http://data.eastmoney.com/DataCenter_V3/jgdy/gsjsdy.ashx?'\n",
    "    param = {\n",
    "        \"pagesize\":100,\n",
    "        \"page\":1,\n",
    "        'sortRule':-1,\n",
    "        'sortType':0,\n",
    "        'code':code\n",
    "    }\n",
    "    ret=requests.get(url,params=param)\n",
    "    \n",
    "    print(json.loads(ret.text))\n",
    "    \n",
    "    return pd.DataFrame(json.loads(ret.text)['data'])\n",
    "\n",
    "get_jgdy_one('000568').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.获取券商月报数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "ename": "UnicodeDecodeError",
     "evalue": "'gb2312' codec can't decode byte 0x81 in position 152: illegal multibyte sequence",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mUnicodeDecodeError\u001b[0m                        Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-16-a8a53ebf6d84>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     17\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_json\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mresponse\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     18\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 19\u001b[1;33m \u001b[0mget_yb\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'201901'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m<ipython-input-16-a8a53ebf6d84>\u001b[0m in \u001b[0;36mget_yb\u001b[1;34m(mon)\u001b[0m\n\u001b[0;32m     10\u001b[0m     \u001b[0mrs\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0murllib\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrequest\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mRequest\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mhttp_url\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mheaders\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mheader_dict\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     11\u001b[0m     \u001b[0mresponse\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0murllib\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrequest\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0murlopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mrs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 12\u001b[1;33m     \u001b[0mtoken\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mre\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msearch\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"token=(.*?)&st\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mresponse\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdecode\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'gb2312'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgroup\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     13\u001b[0m     \u001b[0mday\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mmon\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;36m4\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m+\u001b[0m\u001b[1;34m\"-\"\u001b[0m\u001b[1;33m+\u001b[0m\u001b[0mmon\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m4\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;36m6\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     14\u001b[0m     \u001b[0murl\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m\"http://dcfm.eastmoney.com/em_mutisvcexpandinterface/api/js/get?type=QSYJBG_MReport&token=\"\u001b[0m\u001b[1;33m+\u001b[0m\u001b[0mtoken\u001b[0m\u001b[1;33m+\u001b[0m\u001b[1;34m\"&st=RQ&sr=-1&p=1&ps=1000&amp;js=(x)&filter=(RQ='\"\u001b[0m\u001b[1;33m+\u001b[0m\u001b[0mday\u001b[0m\u001b[1;33m+\u001b[0m\u001b[1;34m\"-01T00:00:00')&rt=51785853\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mUnicodeDecodeError\u001b[0m: 'gb2312' codec can't decode byte 0x81 in position 152: illegal multibyte sequence"
     ]
    }
   ],
   "source": [
    "#-*- coding:utf-8 -*-\n",
    "import urllib\n",
    "import re\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "def get_yb(mon):\n",
    "    http_url = 'http://data.eastmoney.com/other/qsjy/yb.%s.html'%mon\n",
    "    header_dict = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko'}\n",
    "    rs = urllib.request.Request(http_url,headers=header_dict)\n",
    "    response = urllib.request.urlopen(rs)\n",
    "    token = re.search(\"token=(.*?)&st\", response.read().decode('gbk')).group(1)\n",
    "    day = mon[0:4]+\"-\"+mon[4:6]\n",
    "    url = \"http://dcfm.eastmoney.com/em_mutisvcexpandinterface/api/js/get?type=QSYJBG_MReport&token=\"+token+\"&st=RQ&sr=-1&p=1&ps=1000&amp;js=(x)&filter=(RQ='\"+day+\"-01T00:00:00')&rt=51785853\"\n",
    "    rs = urllib.request.Request(url,headers=header_dict)\n",
    "    response = urllib.request.urlopen(rs)\n",
    "    return pd.read_json(response.read())\n",
    "\n",
    "get_yb('201901').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.获取质押比例ok\n",
    "2020-01-10 更新"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>scode</th>\n",
       "      <th>sname</th>\n",
       "      <th>tdate</th>\n",
       "      <th>hy</th>\n",
       "      <th>blfb</th>\n",
       "      <th>amtshareratio</th>\n",
       "      <th>bballowance</th>\n",
       "      <th>zysz</th>\n",
       "      <th>amtsharenum</th>\n",
       "      <th>bbyallowance</th>\n",
       "      <th>bbwallowance</th>\n",
       "      <th>zdf</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tdate</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-17</th>\n",
       "      <td>600741.0</td>\n",
       "      <td>华域汽车</td>\n",
       "      <td>2020-01-17T00:00:00</td>\n",
       "      <td>汽车零部件</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.8</td>\n",
       "      <td>22.888</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-10</th>\n",
       "      <td>600741.0</td>\n",
       "      <td>华域汽车</td>\n",
       "      <td>2020-01-10T00:00:00</td>\n",
       "      <td>汽车零部件</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.8</td>\n",
       "      <td>22.816</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>600741.0</td>\n",
       "      <td>华域汽车</td>\n",
       "      <td>2020-01-03T00:00:00</td>\n",
       "      <td>汽车零部件</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.8</td>\n",
       "      <td>20.872</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-27</th>\n",
       "      <td>600741.0</td>\n",
       "      <td>华域汽车</td>\n",
       "      <td>2019-12-27T00:00:00</td>\n",
       "      <td>汽车零部件</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.8</td>\n",
       "      <td>20.240</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-20</th>\n",
       "      <td>600741.0</td>\n",
       "      <td>华域汽车</td>\n",
       "      <td>2019-12-20T00:00:00</td>\n",
       "      <td>汽车零部件</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.8</td>\n",
       "      <td>19.528</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               scode sname                tdate     hy  blfb  amtshareratio  \\\n",
       "tdate                                                                         \n",
       "2020-01-17  600741.0  华域汽车  2020-01-17T00:00:00  汽车零部件   4.0            0.0   \n",
       "2020-01-10  600741.0  华域汽车  2020-01-10T00:00:00  汽车零部件   4.0            0.0   \n",
       "2020-01-03  600741.0  华域汽车  2020-01-03T00:00:00  汽车零部件   4.0            0.0   \n",
       "2019-12-27  600741.0  华域汽车  2019-12-27T00:00:00  汽车零部件   4.0            0.0   \n",
       "2019-12-20  600741.0  华域汽车  2019-12-20T00:00:00  汽车零部件   4.0            0.0   \n",
       "\n",
       "            bballowance    zysz  amtsharenum  bbyallowance  bbwallowance zdf  \n",
       "tdate                                                                         \n",
       "2020-01-17          0.8  22.888          1.0           0.8           0.0   -  \n",
       "2020-01-10          0.8  22.816          1.0           0.8           0.0   -  \n",
       "2020-01-03          0.8  20.872          1.0           0.8           0.0   -  \n",
       "2019-12-27          0.8  20.240          1.0           0.8           0.0   -  \n",
       "2019-12-20          0.8  19.528          1.0           0.8           0.0   -  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests\n",
    "import demjson\n",
    "\n",
    "def get_zy_ratio(code=None,count=500):\n",
    "    '''code : 标的代码 ,默认为None ,返回全市场质押比例\n",
    "    count : 返回最近的多少条'''\n",
    "    if not code :\n",
    "        url = \"http://dcfm.eastmoney.com/EM_MutiSvcExpandInterface/api/js/get?type=ZD_SUM&token=70f12f2f4f091e459a279469fe49eca5\"\n",
    "    else:\n",
    "        url = \"http://dcfm.eastmoney.com/EM_MutiSvcExpandInterface/api/js/get?type=ZD_QL_LB&token=70f12f2f4f091e459a279469fe49eca5\"\n",
    "    url += \"&js={pages:(tp),data:(x),font:(font)}\"\n",
    "    url += \"&ps={}&filter=(scode={})\".format(count,code)\n",
    "    s = requests.get(url).text\n",
    "    decode_num_data = pd.DataFrame(demjson.decode(s)['font']['FontMapping']).set_index('code').value.astype('str')\n",
    "    for dec in decode_num_data.index:\n",
    "        s = s.replace(dec,decode_num_data[dec])\n",
    "\n",
    "    datas = pd.DataFrame(demjson.decode(s)['data'],dtype='float')\n",
    "    datas.index = pd.to_datetime(datas.tdate)\n",
    "    return datas\n",
    "\n",
    "get_zy_ratio(code='600741',count=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.机构持仓比例(个股明细)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "ename": "JSONDecodeError",
     "evalue": "The input is gibberish, is the Unicode encoding correct?",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mJSONDecodeError\u001b[0m                           Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-12-c3896e087ce2>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     14\u001b[0m     \u001b[1;32mreturn\u001b[0m  \u001b[0mdf\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     15\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 16\u001b[1;33m \u001b[0mget_jgcg\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'600741'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'2019-06-30'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m<ipython-input-12-c3896e087ce2>\u001b[0m in \u001b[0;36mget_jgcg\u001b[1;34m(code, date, stat, count)\u001b[0m\n\u001b[0;32m     11\u001b[0m     \u001b[0murl\u001b[0m \u001b[1;33m+=\u001b[0m \u001b[1;34m\"date={}&code={}&stat={}&ps={}&st=5&sr=-1&p=1\"\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdate\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mcode\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mstat\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mcount\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     12\u001b[0m     \u001b[0mres\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mrequests\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0murl\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreplace\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'var jsname = '\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m\"\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 13\u001b[1;33m     \u001b[0mdf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdemjson\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdecode\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mres\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'data'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     14\u001b[0m     \u001b[1;32mreturn\u001b[0m  \u001b[0mdf\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     15\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\zhuj\\anaconda3\\envs\\basicresearch\\lib\\site-packages\\demjson.py\u001b[0m in \u001b[0;36mdecode\u001b[1;34m(txt, encoding, **kwargs)\u001b[0m\n\u001b[0;32m   5697\u001b[0m                        \u001b[0mencoding\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mencoding\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5698\u001b[0m                        \u001b[0mreturn_errors\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mreturn_errors\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0mwrite_errors\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 5699\u001b[1;33m                        return_stats=(return_stats or write_stats) )\n\u001b[0m\u001b[0;32m   5700\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5701\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mwrite_errors\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\zhuj\\anaconda3\\envs\\basicresearch\\lib\\site-packages\\demjson.py\u001b[0m in \u001b[0;36mdecode\u001b[1;34m(self, txt, encoding, return_errors, return_stats)\u001b[0m\n\u001b[0;32m   4913\u001b[0m             \u001b[0merrors\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0merr\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0merr\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mstate\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0merrors\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mseverity\u001b[0m \u001b[1;32min\u001b[0m \u001b[1;33m(\u001b[0m\u001b[1;34m'fatal'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'error'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4914\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0merrors\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 4915\u001b[1;33m                 \u001b[1;32mraise\u001b[0m \u001b[0merrors\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   4916\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mreturn_stats\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4917\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mresult_type\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstate\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstate\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstats\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mJSONDecodeError\u001b[0m: The input is gibberish, is the Unicode encoding correct?"
     ]
    }
   ],
   "source": [
    "def get_jgcg(code,date,stat=0,count=100):\n",
    "    '''数据来源 http://data.eastmoney.com/zlsj/detail/2019-06-30-0-600741.html\n",
    "    code : 标的代码 \n",
    "    date : 报告期 \n",
    "    stat :  stat(机构类型) :  0-所有;1-基金(官网有数据);2-QFII;3-社保;4-券商;5-保险;6-信托\n",
    "    url中的st,sr等可以自己加参数修改 , 默认按照持股市值降序排序\n",
    "    '''\n",
    "    import pandas as pd\n",
    "    import demjson ,requests\n",
    "    url = \"http://data.eastmoney.com/zlsj/detail.aspx?type=ajax&\"\n",
    "    url += \"date={}&code={}&stat={}&ps={}&st=5&sr=-1&p=1\".format(date,code,stat,count)\n",
    "    res = requests.get(url).text.replace('var jsname = ',\"\")\n",
    "    df = pd.DataFrame(demjson.decode(res)['data'])\n",
    "    return  df\n",
    "\n",
    "get_jgcg('600741','2019-06-30').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "code_folding": []
   },
   "source": [
    "## 11.行业板块的资金流向(最新汇总)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'datetime' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-13-e8cf69fc1118>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     17\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0msum_hy\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mset_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'code'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     18\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 19\u001b[1;33m \u001b[0mget_today_bkzj\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfs\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m2\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m<ipython-input-13-e8cf69fc1118>\u001b[0m in \u001b[0;36mget_today_bkzj\u001b[1;34m(fs)\u001b[0m\n\u001b[0;32m     13\u001b[0m              '超大单净额','超大单占比','大单净额','大单占比','中单净额','中单占比','小单净额','小单占比']\n\u001b[0;32m     14\u001b[0m     \u001b[0msum_hy\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdemjson\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdecode\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mres_\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'data'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'diff'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 15\u001b[1;33m     \u001b[0msum_hy\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mf124\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msum_hy\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'f124'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdatetime\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfromtimestamp\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mx\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     16\u001b[0m     \u001b[0msum_hy\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mindex_\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     17\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0msum_hy\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mset_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'code'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\zhuj\\anaconda3\\envs\\basicresearch\\lib\\site-packages\\pandas\\core\\series.py\u001b[0m in \u001b[0;36mapply\u001b[1;34m(self, func, convert_dtype, args, **kwds)\u001b[0m\n\u001b[0;32m   4211\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4212\u001b[0m                 \u001b[0mvalues\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobject\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_values\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 4213\u001b[1;33m                 \u001b[0mmapped\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mlib\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmap_infer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconvert\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mconvert_dtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   4214\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4215\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmapped\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmapped\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mSeries\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\lib.pyx\u001b[0m in \u001b[0;36mpandas._libs.lib.map_infer\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32m<ipython-input-13-e8cf69fc1118>\u001b[0m in \u001b[0;36m<lambda>\u001b[1;34m(x)\u001b[0m\n\u001b[0;32m     13\u001b[0m              '超大单净额','超大单占比','大单净额','大单占比','中单净额','中单占比','小单净额','小单占比']\n\u001b[0;32m     14\u001b[0m     \u001b[0msum_hy\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdemjson\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdecode\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mres_\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'data'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'diff'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 15\u001b[1;33m     \u001b[0msum_hy\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mf124\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msum_hy\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'f124'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdatetime\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfromtimestamp\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mx\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     16\u001b[0m     \u001b[0msum_hy\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mindex_\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     17\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0msum_hy\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mset_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'code'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'datetime' is not defined"
     ]
    }
   ],
   "source": [
    "def get_today_bkzj(fs=2):\n",
    "    \"\"\" 获取当天总的资金流向数据 , 含有板块的code及名称\n",
    "    数据来源  http://data.eastmoney.com/bkzj/hy.html\n",
    "    fs= 3#概念\n",
    "    fs= 2#行业\n",
    "    fs= 1#地域 \"\"\"\n",
    "    import demjson, json\n",
    "    import pandas as pd\n",
    "    url_ = \"http://push2.eastmoney.com/api/qt/clist/get?pn=1&pz=100&po=1&np=1&fltt=2&invt=2&fid=f62&fs=m:90+t:{}\".format(fs)\n",
    "    url_ += \"&stat=1&fields=f12,f14,f2,f3,f62,f184,f66,f69,f72,f75,f78,f81,f84,f87,f204,f205,f124\"\n",
    "    res_ = requests.get(url_).text\n",
    "    index_ = ['code','日期','行业名称','主力占比','??','主力流入最大股','主力流入最大股code','?','今日涨跌幅','主力净额',\n",
    "             '超大单净额','超大单占比','大单净额','大单占比','中单净额','中单占比','小单净额','小单占比']\n",
    "    sum_hy = pd.DataFrame(demjson.decode(res_)['data']['diff'])\n",
    "    sum_hy.f124 = sum_hy['f124'].apply(lambda x: datetime.datetime.fromtimestamp(x))\n",
    "    sum_hy.columns = index_\n",
    "    return sum_hy.set_index('code')\n",
    "\n",
    "get_today_bkzj(fs=2) .head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.行业概念资金流向历史数据ok"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>主力净流入</th>\n",
       "      <th>小单净流入</th>\n",
       "      <th>中单净流入</th>\n",
       "      <th>大单净流入</th>\n",
       "      <th>超大单净流入</th>\n",
       "      <th>主力占比</th>\n",
       "      <th>小单占比</th>\n",
       "      <th>中单占比</th>\n",
       "      <th>大单占比</th>\n",
       "      <th>超大单占比</th>\n",
       "      <th>??</th>\n",
       "      <th>??</th>\n",
       "      <th>??</th>\n",
       "      <th>??</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-11-23</td>\n",
       "      <td>-990156864.0</td>\n",
       "      <td>715625728.0</td>\n",
       "      <td>274531328.0</td>\n",
       "      <td>-582042368.0</td>\n",
       "      <td>-408114496.0</td>\n",
       "      <td>-4.70</td>\n",
       "      <td>3.39</td>\n",
       "      <td>1.30</td>\n",
       "      <td>-2.76</td>\n",
       "      <td>-1.94</td>\n",
       "      <td>1516.04</td>\n",
       "      <td>0.92</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-24</td>\n",
       "      <td>718804720.0</td>\n",
       "      <td>-289730560.0</td>\n",
       "      <td>-429074432.0</td>\n",
       "      <td>235644416.0</td>\n",
       "      <td>483160304.0</td>\n",
       "      <td>3.02</td>\n",
       "      <td>-1.22</td>\n",
       "      <td>-1.80</td>\n",
       "      <td>0.99</td>\n",
       "      <td>2.03</td>\n",
       "      <td>1546.49</td>\n",
       "      <td>2.01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-25</td>\n",
       "      <td>280972544.0</td>\n",
       "      <td>-200727376.0</td>\n",
       "      <td>-80245248.0</td>\n",
       "      <td>242463504.0</td>\n",
       "      <td>38509040.0</td>\n",
       "      <td>2.19</td>\n",
       "      <td>-1.56</td>\n",
       "      <td>-0.62</td>\n",
       "      <td>1.89</td>\n",
       "      <td>0.30</td>\n",
       "      <td>1567.59</td>\n",
       "      <td>1.36</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-26</td>\n",
       "      <td>-1173984.0</td>\n",
       "      <td>487515120.0</td>\n",
       "      <td>-486341120.0</td>\n",
       "      <td>-82951312.0</td>\n",
       "      <td>81777328.0</td>\n",
       "      <td>-0.01</td>\n",
       "      <td>3.10</td>\n",
       "      <td>-3.09</td>\n",
       "      <td>-0.53</td>\n",
       "      <td>0.52</td>\n",
       "      <td>1570.28</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-11-29</td>\n",
       "      <td>-1338274256.0</td>\n",
       "      <td>1096295776.0</td>\n",
       "      <td>241978368.0</td>\n",
       "      <td>-487465472.0</td>\n",
       "      <td>-850808784.0</td>\n",
       "      <td>-7.37</td>\n",
       "      <td>6.04</td>\n",
       "      <td>1.33</td>\n",
       "      <td>-2.68</td>\n",
       "      <td>-4.68</td>\n",
       "      <td>1560.68</td>\n",
       "      <td>-0.61</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date          主力净流入         小单净流入         中单净流入         大单净流入  \\\n",
       "0  2021-11-23   -990156864.0   715625728.0   274531328.0  -582042368.0   \n",
       "1  2021-11-24    718804720.0  -289730560.0  -429074432.0   235644416.0   \n",
       "2  2021-11-25    280972544.0  -200727376.0   -80245248.0   242463504.0   \n",
       "3  2021-11-26     -1173984.0   487515120.0  -486341120.0   -82951312.0   \n",
       "4  2021-11-29  -1338274256.0  1096295776.0   241978368.0  -487465472.0   \n",
       "\n",
       "         超大单净流入   主力占比   小单占比   中单占比   大单占比  超大单占比       ??     ??    ??    ??  \n",
       "0  -408114496.0  -4.70   3.39   1.30  -2.76  -1.94  1516.04   0.92  0.00  0.00  \n",
       "1   483160304.0   3.02  -1.22  -1.80   0.99   2.03  1546.49   2.01  0.00  0.00  \n",
       "2    38509040.0   2.19  -1.56  -0.62   1.89   0.30  1567.59   1.36  0.00  0.00  \n",
       "3    81777328.0  -0.01   3.10  -3.09  -0.53   0.52  1570.28   0.17  0.00  0.00  \n",
       "4  -850808784.0  -7.37   6.04   1.33  -2.68  -4.68  1560.68  -0.61  0.00  0.00  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_bk_moneyflow(code,count=100):\n",
    "    \"\"\" code 可以从 get_today_bkzj 获取到的数据中取\n",
    "    count : 返回条数 (0为无限制) , 这个数据最多只能查询最近100条左右\n",
    "    \"\"\"    \n",
    "    url =\"http://push2his.eastmoney.com/api/qt/stock/fflow/daykline/get?\"\n",
    "    url += \"lmt={}&secid=90.{}&fields1=f1,f2,f3,f7\".format(count,code)\n",
    "    url += \"&fields2=f51,f52,f53,f54,f55,f56,f57,f58,f59,f60,f61,f62,f63,f64,f65\" \n",
    "    res = requests.get(url).text\n",
    "    dic = pd.Series(demjson.decode(res)['data']['klines']).str.split(',').to_dict() \n",
    "    index = ['date','主力净流入','小单净流入','中单净流入','大单净流入','超大单净流入','主力占比','小单占比','中单占比','大单占比','超大单占比',\n",
    "             '??','??','??',\"??\"]\n",
    "    return pd.DataFrame(dic,index=index).T\n",
    "\n",
    "get_bk_moneyflow(\"BK0727\",5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.大宗交易数据(市场统计,个股统计,个股明细)ok"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SECUCODE</th>\n",
       "      <th>SNAME</th>\n",
       "      <th>RCHANGE</th>\n",
       "      <th>CPRICE</th>\n",
       "      <th>PRICE</th>\n",
       "      <th>Zyl</th>\n",
       "      <th>CJCount</th>\n",
       "      <th>CJZE</th>\n",
       "      <th>CJZL</th>\n",
       "      <th>YSSLTAG</th>\n",
       "      <th>Cjeltszb</th>\n",
       "      <th>RCHANGE1DC</th>\n",
       "      <th>RCHANGE5DC</th>\n",
       "      <th>RCHANGE10DC</th>\n",
       "      <th>RCHANGE20DC</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TDATE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-10</th>\n",
       "      <td>600603</td>\n",
       "      <td>广汇物流</td>\n",
       "      <td>0.8048</td>\n",
       "      <td>5.01</td>\n",
       "      <td>4.530000</td>\n",
       "      <td>-0.095808</td>\n",
       "      <td>1</td>\n",
       "      <td>11325.00</td>\n",
       "      <td>2500.00</td>\n",
       "      <td>510426189</td>\n",
       "      <td>0.044286</td>\n",
       "      <td>1.5968</td>\n",
       "      <td>-0.3992</td>\n",
       "      <td>-0.1996</td>\n",
       "      <td>5.3892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-10</th>\n",
       "      <td>2249</td>\n",
       "      <td>大洋电机</td>\n",
       "      <td>-1.9465</td>\n",
       "      <td>4.03</td>\n",
       "      <td>3.900000</td>\n",
       "      <td>-0.032258</td>\n",
       "      <td>1</td>\n",
       "      <td>12420.14</td>\n",
       "      <td>3184.65</td>\n",
       "      <td>1637450320</td>\n",
       "      <td>0.018821</td>\n",
       "      <td>0.4963</td>\n",
       "      <td>0.9926</td>\n",
       "      <td>-14.1439</td>\n",
       "      <td>-2.4814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-10</th>\n",
       "      <td>300575</td>\n",
       "      <td>中旗股份</td>\n",
       "      <td>2.2509</td>\n",
       "      <td>27.71</td>\n",
       "      <td>24.930000</td>\n",
       "      <td>-0.100325</td>\n",
       "      <td>1</td>\n",
       "      <td>2493.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>73818001</td>\n",
       "      <td>0.012188</td>\n",
       "      <td>1.9488</td>\n",
       "      <td>1.7683</td>\n",
       "      <td>-14.2548</td>\n",
       "      <td>2.3818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-10</th>\n",
       "      <td>300510</td>\n",
       "      <td>金冠股份</td>\n",
       "      <td>-5.5405</td>\n",
       "      <td>6.99</td>\n",
       "      <td>6.821328</td>\n",
       "      <td>-0.024130</td>\n",
       "      <td>5</td>\n",
       "      <td>5804.95</td>\n",
       "      <td>851.00</td>\n",
       "      <td>749852220</td>\n",
       "      <td>0.011075</td>\n",
       "      <td>-3.5765</td>\n",
       "      <td>-9.2990</td>\n",
       "      <td>-30.0429</td>\n",
       "      <td>-21.4592</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-10</th>\n",
       "      <td>2111</td>\n",
       "      <td>威海广泰</td>\n",
       "      <td>-1.0379</td>\n",
       "      <td>16.21</td>\n",
       "      <td>15.999883</td>\n",
       "      <td>-0.012962</td>\n",
       "      <td>2</td>\n",
       "      <td>5470.84</td>\n",
       "      <td>341.93</td>\n",
       "      <td>340285644</td>\n",
       "      <td>0.009918</td>\n",
       "      <td>1.8507</td>\n",
       "      <td>2.0975</td>\n",
       "      <td>-11.7212</td>\n",
       "      <td>3.7014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            SECUCODE SNAME  RCHANGE  CPRICE      PRICE       Zyl  CJCount  \\\n",
       "TDATE                                                                       \n",
       "2020-01-10    600603  广汇物流   0.8048    5.01   4.530000 -0.095808        1   \n",
       "2020-01-10      2249  大洋电机  -1.9465    4.03   3.900000 -0.032258        1   \n",
       "2020-01-10    300575  中旗股份   2.2509   27.71  24.930000 -0.100325        1   \n",
       "2020-01-10    300510  金冠股份  -5.5405    6.99   6.821328 -0.024130        5   \n",
       "2020-01-10      2111  威海广泰  -1.0379   16.21  15.999883 -0.012962        2   \n",
       "\n",
       "                CJZE     CJZL     YSSLTAG  Cjeltszb  RCHANGE1DC  RCHANGE5DC  \\\n",
       "TDATE                                                                         \n",
       "2020-01-10  11325.00  2500.00   510426189  0.044286      1.5968     -0.3992   \n",
       "2020-01-10  12420.14  3184.65  1637450320  0.018821      0.4963      0.9926   \n",
       "2020-01-10   2493.00   100.00    73818001  0.012188      1.9488      1.7683   \n",
       "2020-01-10   5804.95   851.00   749852220  0.011075     -3.5765     -9.2990   \n",
       "2020-01-10   5470.84   341.93   340285644  0.009918      1.8507      2.0975   \n",
       "\n",
       "            RCHANGE10DC  RCHANGE20DC  \n",
       "TDATE                                 \n",
       "2020-01-10      -0.1996       5.3892  \n",
       "2020-01-10     -14.1439      -2.4814  \n",
       "2020-01-10     -14.2548       2.3818  \n",
       "2020-01-10     -30.0429     -21.4592  \n",
       "2020-01-10     -11.7212       3.7014  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests ,pandas as pd\n",
    "def get_dzjy(typ='mrmx',code=None,start_date=None,end_date=None,sort_type=\"TRADE\",count=100):\n",
    "    \"\"\"数据来源 http://data.eastmoney.com/dzjy/dzjy_mrtj.aspx\n",
    "    typ :  mrtjgg-每日统计(个股); mrtjsc-每日统计(市场); mrmx-个股明细\n",
    "    code : 标的代码, 可选参数 ,typ为市场统计时不生效\n",
    "    sort_type : 排序的字段 ,url的sr参数可以选定降序还是升序,需要的话可以自己加参数 , 默认日期降序\n",
    "    count : 最大返回数量\n",
    "    start_date 和end_date : 选定时间段 , 只填写end_date时返回end_date那天的数据 , end_date和start_date都填写时返回数据大于start_date且小于end_date, 默认为None表示不限制 ,\n",
    "    \"\"\"\n",
    "    if typ=='mrtjgg' : type_ = \"DZJYGGTJ\"\n",
    "    elif typ=='mrtjsc' : type_ = \"DZJYSCTJ\"\n",
    "    elif typ=='mrmx'   : type_ = \"DZJYXQ\"\n",
    "    else : raise TypeError(\"typ不正确\")\n",
    "    if not start_date and not end_date : start_date ='1999-01-01';end_date='9999-12-31'\n",
    "    if not start_date : start_date=end_date\n",
    "    if not end_date : end_date= '9999-12-31'\n",
    "    if not  code : code=\"\"\n",
    "    url = \"http://dcfm.eastmoney.com/em_mutisvcexpandinterface/api/js/get?token=70f12f2f4f091e459a279469fe49eca5&type={}\".format(type_)\n",
    "    url += \"&st={}&sr=-1&p=1&ps={}\".format(sort_type,count)\n",
    "    url += \"&filter=(TDATE>= ^{}^ and TDATE <= ^{}^)\".format(start_date,end_date)\n",
    "    url += \"(SECUCODE={})\".format(code)\n",
    "\n",
    "    res = requests.get(url).text\n",
    "    try :\n",
    "        df = pd.read_json(res)\n",
    "        df.TDATE = pd.to_datetime(df.TDATE)\n",
    "    except :\n",
    "        raise ValueError(\"数据不存在/返回数据错误 ,请检查筛选条件 , 返回的数据  :  \"+ res)\n",
    "    return df.set_index('TDATE')\n",
    "\n",
    "# 获取 2020-01-10 那一天 个股汇总中, 大宗交易成交总额/流通市值排名前5的数据\n",
    "get_dzjy('mrtjgg',end_date='2020-01-10',sort_type = \"Cjeltszb\",count=5) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 抓取港股新股数据统计打新收益\n",
    "作者:止一之路 原文链接: https://www.joinquant.com/view/community/detail/e935b94c5e47373c7689e5132b02825a?type=1  \n",
    "**内容较多,请直接点击原文链接查**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "****\n",
    "# 期货类"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. 生意社大宗商品报价数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "ename": "ImportError",
     "evalue": "lxml not found, please install it",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mImportError\u001b[0m                               Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-15-77fc3334adfe>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     12\u001b[0m     \u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'price'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'报价'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mre\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfindall\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mr\"\\d+\\.?\\d*\"\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mx\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mint\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     13\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 14\u001b[1;33m \u001b[0mget_sys\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'806'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m<ipython-input-15-77fc3334adfe>\u001b[0m in \u001b[0;36mget_sys\u001b[1;34m(symbol, page)\u001b[0m\n\u001b[0;32m      9\u001b[0m     \u001b[0mtxt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mrequests\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'http://www.100ppi.com/mprice/plist-'\u001b[0m\u001b[1;33m+\u001b[0m\u001b[0msymbol\u001b[0m\u001b[1;33m+\u001b[0m\u001b[1;34m'-'\u001b[0m\u001b[1;33m+\u001b[0m\u001b[0mstr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpage\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m+\u001b[0m\u001b[1;34m'.html'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     10\u001b[0m     \u001b[0mtable\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mBeautifulSoup\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtxt\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfind_all\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"table\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mclass_\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"lp-table mb15\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 11\u001b[1;33m     \u001b[0mdf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_html\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtable\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mheader\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     12\u001b[0m     \u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'price'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'报价'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mre\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfindall\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mr\"\\d+\\.?\\d*\"\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mx\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mint\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     13\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\zhuj\\anaconda3\\envs\\basicresearch\\lib\\site-packages\\pandas\\util\\_decorators.py\u001b[0m in \u001b[0;36mwrapper\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    294\u001b[0m                 )\n\u001b[0;32m    295\u001b[0m                 \u001b[0mwarnings\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwarn\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmsg\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mFutureWarning\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstacklevel\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstacklevel\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 296\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    297\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    298\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mwrapper\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\zhuj\\anaconda3\\envs\\basicresearch\\lib\\site-packages\\pandas\\io\\html.py\u001b[0m in \u001b[0;36mread_html\u001b[1;34m(io, match, flavor, header, index_col, skiprows, attrs, parse_dates, thousands, encoding, decimal, converters, na_values, keep_default_na, displayed_only)\u001b[0m\n\u001b[0;32m   1099\u001b[0m         \u001b[0mna_values\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mna_values\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1100\u001b[0m         \u001b[0mkeep_default_na\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mkeep_default_na\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1101\u001b[1;33m         \u001b[0mdisplayed_only\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdisplayed_only\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1102\u001b[0m     )\n",
      "\u001b[1;32mc:\\users\\zhuj\\anaconda3\\envs\\basicresearch\\lib\\site-packages\\pandas\\io\\html.py\u001b[0m in \u001b[0;36m_parse\u001b[1;34m(flavor, io, match, attrs, encoding, displayed_only, **kwargs)\u001b[0m\n\u001b[0;32m    892\u001b[0m     \u001b[0mretained\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    893\u001b[0m     \u001b[1;32mfor\u001b[0m \u001b[0mflav\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mflavor\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 894\u001b[1;33m         \u001b[0mparser\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_parser_dispatch\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mflav\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    895\u001b[0m         \u001b[0mp\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mparser\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mio\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcompiled_match\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mattrs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mencoding\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdisplayed_only\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    896\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\zhuj\\anaconda3\\envs\\basicresearch\\lib\\site-packages\\pandas\\io\\html.py\u001b[0m in \u001b[0;36m_parser_dispatch\u001b[1;34m(flavor)\u001b[0m\n\u001b[0;32m    849\u001b[0m     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    850\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0m_HAS_LXML\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 851\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mImportError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"lxml not found, please install it\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    852\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0m_valid_parsers\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mflavor\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    853\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mImportError\u001b[0m: lxml not found, please install it"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "def get_sys(symbol='',page=1):\n",
    "    \"\"\"\n",
    "    查询生意社大宗商品报价数据。\n",
    "    symbol: 生意社网页上对品种的编号,打开对应品种直接可以从url上看到 如806代表甲醚\n",
    "    page: 查询第几页\"\"\"\n",
    "    txt = requests.get('http://www.100ppi.com/mprice/plist-'+symbol+'-'+str(page)+'.html').text\n",
    "    table = BeautifulSoup(txt).find_all(\"table\", class_=\"lp-table mb15\")\n",
    "    df = pd.read_html(str(table),header=0)[0]\n",
    "    df['price'] = df['报价'].apply(lambda x: re.findall(r\"\\d+\\.?\\d*\",x)[0]).astype(int)\n",
    "    return df\n",
    "get_sys('806').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.生意社商品现价,基差数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "ename": "ImportError",
     "evalue": "lxml not found, please install it",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mImportError\u001b[0m                               Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-20-8c747b7af0a3>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     10\u001b[0m     \u001b[0mdata\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;34m'品种'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'现货价格'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'临近交割合约'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'合约价格1'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'基差1'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'主力合约'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'合约价格2'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'基差2'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     11\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mdata\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mset_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'品种'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 12\u001b[1;33m \u001b[0mget_basis\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m<ipython-input-20-8c747b7af0a3>\u001b[0m in \u001b[0;36mget_basis\u001b[1;34m(date)\u001b[0m\n\u001b[0;32m      7\u001b[0m     \u001b[1;31m# pd.read_html(r.text)[0][1][1]#.iloc\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      8\u001b[0m     \u001b[0mr\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mrequests\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mu2\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mtimeout\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m2\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 9\u001b[1;33m     \u001b[0mdata\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_html\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdropna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdrop\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcolumns\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;36m4\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m6\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m9\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m11\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     10\u001b[0m     \u001b[0mdata\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;34m'品种'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'现货价格'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'临近交割合约'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'合约价格1'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'基差1'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'主力合约'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'合约价格2'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'基差2'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     11\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mdata\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mset_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'品种'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\zhuj\\anaconda3\\envs\\basicresearch\\lib\\site-packages\\pandas\\util\\_decorators.py\u001b[0m in \u001b[0;36mwrapper\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    294\u001b[0m                 )\n\u001b[0;32m    295\u001b[0m                 \u001b[0mwarnings\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwarn\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmsg\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mFutureWarning\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstacklevel\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstacklevel\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 296\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    297\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    298\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mwrapper\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\zhuj\\anaconda3\\envs\\basicresearch\\lib\\site-packages\\pandas\\io\\html.py\u001b[0m in \u001b[0;36mread_html\u001b[1;34m(io, match, flavor, header, index_col, skiprows, attrs, parse_dates, thousands, encoding, decimal, converters, na_values, keep_default_na, displayed_only)\u001b[0m\n\u001b[0;32m   1099\u001b[0m         \u001b[0mna_values\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mna_values\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1100\u001b[0m         \u001b[0mkeep_default_na\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mkeep_default_na\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1101\u001b[1;33m         \u001b[0mdisplayed_only\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdisplayed_only\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1102\u001b[0m     )\n",
      "\u001b[1;32mc:\\users\\zhuj\\anaconda3\\envs\\basicresearch\\lib\\site-packages\\pandas\\io\\html.py\u001b[0m in \u001b[0;36m_parse\u001b[1;34m(flavor, io, match, attrs, encoding, displayed_only, **kwargs)\u001b[0m\n\u001b[0;32m    892\u001b[0m     \u001b[0mretained\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    893\u001b[0m     \u001b[1;32mfor\u001b[0m \u001b[0mflav\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mflavor\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 894\u001b[1;33m         \u001b[0mparser\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_parser_dispatch\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mflav\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    895\u001b[0m         \u001b[0mp\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mparser\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mio\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcompiled_match\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mattrs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mencoding\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdisplayed_only\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    896\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\zhuj\\anaconda3\\envs\\basicresearch\\lib\\site-packages\\pandas\\io\\html.py\u001b[0m in \u001b[0;36m_parser_dispatch\u001b[1;34m(flavor)\u001b[0m\n\u001b[0;32m    849\u001b[0m     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    850\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0m_HAS_LXML\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 851\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mImportError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"lxml not found, please install it\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    852\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0m_valid_parsers\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mflavor\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    853\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mImportError\u001b[0m: lxml not found, please install it"
     ]
    }
   ],
   "source": [
    "#  生意社商品现价,基差数据\n",
    "import requests\n",
    "import pandas as pd\n",
    "def get_basis(date='2019-01-05'):\n",
    "    u2 = 'http://www.100ppi.com/sf/day-2019-05-29.html'\n",
    "    r=requests.get(u2,timeout=2)\n",
    "    # pd.read_html(r.text)[0][1][1]#.iloc\n",
    "    r=requests.get(u2,timeout=2)\n",
    "    data = pd.read_html(r.text)[1].dropna().drop(columns = [4,6,9,11])\n",
    "    data.columns = ['品种','现货价格','临近交割合约','合约价格1','基差1','主力合约','合约价格2','基差2']\n",
    "    return data.set_index('品种')\n",
    "get_basis().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "****\n",
    "# 其他"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.获取国债收益率数据\n",
    "作者：tinysnowing 链接：https://www.joinquant.com/view/community/detail/13620"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "ename": "JSONDecodeError",
     "evalue": "Expecting value: line 1 column 1 (char 0)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mJSONDecodeError\u001b[0m                           Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-14-1541a20d86c6>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     31\u001b[0m     \u001b[1;31m#print(bag.head())\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     32\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mbag\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 33\u001b[1;33m \u001b[0mget_bnd_yields\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m<ipython-input-14-1541a20d86c6>\u001b[0m in \u001b[0;36mget_bnd_yields\u001b[1;34m(years)\u001b[0m\n\u001b[0;32m     28\u001b[0m     \u001b[0mbag\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     29\u001b[0m     \u001b[1;32mfor\u001b[0m \u001b[0myr\u001b[0m \u001b[1;32min\u001b[0m \u001b[0myears\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 30\u001b[1;33m         \u001b[0mbag\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconcat\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mbag\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mget_bnd_yield\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0myear\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0myr\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     31\u001b[0m     \u001b[1;31m#print(bag.head())\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     32\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mbag\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-14-1541a20d86c6>\u001b[0m in \u001b[0;36mget_bnd_yield\u001b[1;34m(year)\u001b[0m\n\u001b[0;32m     16\u001b[0m     \u001b[0mheaders\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'User-Agent'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m'Mozilla/5.0 (Windows NT 6.1; Win64; x64)'\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     17\u001b[0m     \u001b[0mres\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mrequests\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0murl\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mheaders\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mheaders\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 18\u001b[1;33m     \u001b[0mres\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mjson\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloads\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mres\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcontent\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdecode\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'utf-8'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreplace\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"'\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"\\\"\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     19\u001b[0m     \u001b[0mdata\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mres\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'candles'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     20\u001b[0m     \u001b[0mdata\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdata\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0miloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m:\u001b[0m\u001b[1;36m2\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\zhuj\\anaconda3\\envs\\basicresearch\\lib\\json\\__init__.py\u001b[0m in \u001b[0;36mloads\u001b[1;34m(s, encoding, cls, object_hook, parse_float, parse_int, parse_constant, object_pairs_hook, **kw)\u001b[0m\n\u001b[0;32m    352\u001b[0m             \u001b[0mparse_int\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mparse_float\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m \u001b[1;32mand\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    353\u001b[0m             parse_constant is None and object_pairs_hook is None and not kw):\n\u001b[1;32m--> 354\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0m_default_decoder\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdecode\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    355\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mcls\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    356\u001b[0m         \u001b[0mcls\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mJSONDecoder\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\zhuj\\anaconda3\\envs\\basicresearch\\lib\\json\\decoder.py\u001b[0m in \u001b[0;36mdecode\u001b[1;34m(self, s, _w)\u001b[0m\n\u001b[0;32m    337\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    338\u001b[0m         \"\"\"\n\u001b[1;32m--> 339\u001b[1;33m         \u001b[0mobj\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mend\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraw_decode\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0midx\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0m_w\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;36m0\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    340\u001b[0m         \u001b[0mend\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_w\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mend\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    341\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mend\u001b[0m \u001b[1;33m!=\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\zhuj\\anaconda3\\envs\\basicresearch\\lib\\json\\decoder.py\u001b[0m in \u001b[0;36mraw_decode\u001b[1;34m(self, s, idx)\u001b[0m\n\u001b[0;32m    355\u001b[0m             \u001b[0mobj\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mend\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mscan_once\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0midx\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    356\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mStopIteration\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 357\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mJSONDecodeError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"Expecting value\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0ms\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    358\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mobj\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mend\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mJSONDecodeError\u001b[0m: Expecting value: line 1 column 1 (char 0)"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "import json\n",
    "import pandas as pd\n",
    "import time\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "def get_bnd_yield(year=10):\n",
    "    ids = {10: '29227', 5: '29234', 1: '29231'}\n",
    "    url = 'https://cn.investing.com/common/modules/js_instrument_chart/api/data.php?' + \\\n",
    "        'pair_id={}&pair_id_for_news={}'.format(ids[year], ids[year]) +\\\n",
    "        '&chart_type=area&pair_interval=month&candle_count=120&events=yes&volume_series=yes&period=5-years'\n",
    "    headers = {}\n",
    "    headers['X-Requested-With'] = 'XMLHttpRequest'\n",
    "    headers['Host'] = 'cn.investing.com'\n",
    "    headers['Referer'] = 'https://cn.investing.com/rates-bonds/china-{}-year-bond-yield'.format(year)\n",
    "    headers['User-Agent'] = 'Mozilla/5.0 (Windows NT 6.1; Win64; x64)'\n",
    "    res = requests.get(url, headers=headers)\n",
    "    res = json.loads(res.content.decode('utf-8').replace(\"'\", \"\\\"\"))\n",
    "    data = pd.DataFrame(res['candles'])\n",
    "    data = data.iloc[:, :2]\n",
    "    data.columns = ['date', 'y'+str(year)]\n",
    "    data['date'] = data['date'].map(lambda x: time.strftime(\"%Y-%m-%d\", time.localtime(int(str(x)[:10]))))\n",
    "    data.set_index('date', inplace=True)\n",
    "    return data\n",
    "\n",
    "\n",
    "def get_bnd_yields(years=[1, 5, 10]):\n",
    "    bag = pd.DataFrame()\n",
    "    for yr in years:\n",
    "        bag = pd.concat([bag, get_bnd_yield(year=yr)], axis=1)\n",
    "    #print(bag.head())\n",
    "    return bag\n",
    "get_bnd_yields().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "MarkDown菜单",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
